{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         NaN\n",
       "1    1.189713\n",
       "2         NaN\n",
       "3   -0.806695\n",
       "4         NaN\n",
       "5    0.155311\n",
       "dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example one ：用特定于分组的值填充缺失值\n",
    "\n",
    "# 对于缺失值的处理\n",
    "#  1、用 dropna 将其替换掉\n",
    "#  2、用一个固定值填充 NA 值，fillna 工具\n",
    "#  3、有数据集本身所衍生出来的值填充 NA 值，用 fillna 功能\n",
    "\n",
    "# 用平均值填充 NA 值\n",
    "s = pd.Series(np.random.randn(6)) \n",
    "s[::2] = np.nan\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.179443\n",
       "1    1.189713\n",
       "2    0.179443\n",
       "3   -0.806695\n",
       "4    0.179443\n",
       "5    0.155311\n",
       "dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using the mean of dataset filling the NA \n",
    "s.fillna(s.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio         -0.659016\n",
       "New York      1.064082\n",
       "Vermont       2.020960\n",
       "Florida       1.450527\n",
       "Oregon       -0.154759\n",
       "Nevada        1.347925\n",
       "California    0.062896\n",
       "Idaho         0.576026\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 假设需要对不同的分组填充不同的值\n",
    "# 方法：将数据分组，并使用 apply 和一个能够各个数据块调用 fillna 的函数即可\n",
    "\n",
    "# 以下是一些关于美国几个州的示例数据，这些州被分为 东部 和 西部\n",
    "states = ['Ohio', 'New York', 'Vermont', 'Florida',\n",
    "        'Oregon', 'Nevada', 'California', 'Idaho']\n",
    "\n",
    "# ['East'] * 4产生了一个列表， 包括了['East']中元素的四个拷贝。 将这些列表串联起来\n",
    "group_key = ['East'] * 4 + ['West'] * 4\n",
    "\n",
    "data = pd.Series(np.random.randn(8), index=states)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio         -0.659016\n",
       "New York      1.064082\n",
       "Vermont            NaN\n",
       "Florida       1.450527\n",
       "Oregon       -0.154759\n",
       "Nevada             NaN\n",
       "California    0.062896\n",
       "Idaho              NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将一些值设置为缺失\n",
    "data[['Vermont', 'Nevada', 'Idaho']] = np.nan\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "East    0.618531\n",
       "West   -0.045931\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对不同的分组计算对应分组的平均值\n",
    "data.groupby(group_key).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio         -0.659016\n",
       "New York      1.064082\n",
       "Vermont       0.618531\n",
       "Florida       1.450527\n",
       "Oregon       -0.154759\n",
       "Nevada       -0.045931\n",
       "California    0.062896\n",
       "Idaho        -0.045931\n",
       "dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 编写 apply 函数, 用数据集的平均值填充 NA 值\n",
    "fill_mean = lambda g: g.fillna(g.mean())\n",
    "\n",
    "data.groupby(group_key).apply(fill_mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ohio         -0.659016\n",
       "New York      1.064082\n",
       "Vermont       0.500000\n",
       "Florida       1.450527\n",
       "Oregon       -0.154759\n",
       "Nevada       -1.000000\n",
       "California    0.062896\n",
       "Idaho        -1.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 另外，也可以在代码中预定义各组的填充值\n",
    "# 由于分组具有一个 name 属性，可以用来 Mark 一下\n",
    "fill_values = {'East': 0.5, 'West': -1}\n",
    "\n",
    "fill_func = lambda g: g.fillna(fill_values[g.name]) \n",
    "\n",
    "data.groupby(group_key).apply(fill_func)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AH      1\n",
       "2H      2\n",
       "3H      3\n",
       "4H      4\n",
       "5H      5\n",
       "6H      6\n",
       "7H      7\n",
       "8H      8\n",
       "9H      9\n",
       "10H    10\n",
       "JH     10\n",
       "KH     10\n",
       "QH     10\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example two ：随机采样和排列\n",
    "\n",
    "# 假设想要从一个大数据集中随机抽取（进行替换或者不替换）样本\n",
    "#    以进行 模特卡罗模拟 （ Monte Carlo simulation ）或者其他分析工作\n",
    "\n",
    "# Ctrl + B 全屏代码编辑区 ！！！！！\n",
    "\n",
    "# 抽取的方式很多，其中之一就是对 Series 使用 sample 方法 \n",
    "# Hearts, Spades, Clubs, Diamonds\n",
    "suits = ['H', 'S', 'C', 'D']\n",
    "\n",
    "card_val = (list(range(1, 11)) + [10] * 3) * 4\n",
    "\n",
    "base_names = ['A'] + list(range(2, 11)) + ['J', 'K', 'Q']\n",
    "\n",
    "cards = []\n",
    "for suit in suits:\n",
    "    cards.extend(str(num) + suit for num in base_names)\n",
    "    \n",
    "# deck 就是一副纸牌，有 52 个 Series ，其索引包括牌名，值则是21点或者其他游戏中用于计分的点数\n",
    "deck = pd.Series(card_val, index=cards)\n",
    "deck[:13]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "KC     10\n",
       "10C    10\n",
       "JC     10\n",
       "6S      6\n",
       "9S      9\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 现在，根据上面所讲的，从整副牌中抽出 5 张\n",
    "def draw(deck, n=5):\n",
    "    return deck.sample(n)\n",
    "\n",
    "draw(deck)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "C  2C     2\n",
       "   5C     5\n",
       "D  KD    10\n",
       "   QD    10\n",
       "H  5H     5\n",
       "   9H     9\n",
       "S  6S     6\n",
       "   4S     4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 假设想要从每种花色中随机抽取两张牌\n",
    "# 由于花色是牌名的最后一个字符，所以可据此进行分组，并使用 apply\n",
    "get_suit = lambda card: card[-1]\n",
    "\n",
    "deck.groupby(get_suit).apply(draw, n=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5C      5\n",
       "4C      4\n",
       "10D    10\n",
       "5D      5\n",
       "KH     10\n",
       "7H      7\n",
       "JS     10\n",
       "2S      2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以上 代码 可等价于 如下 代码\n",
    "# 不需要索引，禁用这个功能，提高代码效率\n",
    "deck.groupby(get_suit, group_keys=False).apply(draw, n=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category</th>\n",
       "      <th>data</th>\n",
       "      <th>weights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>0.246188</td>\n",
       "      <td>0.001492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>-1.459589</td>\n",
       "      <td>0.290795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a</td>\n",
       "      <td>-1.296136</td>\n",
       "      <td>0.885833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>0.069525</td>\n",
       "      <td>0.233582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b</td>\n",
       "      <td>0.532954</td>\n",
       "      <td>0.593210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b</td>\n",
       "      <td>-1.948749</td>\n",
       "      <td>0.347098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b</td>\n",
       "      <td>0.347697</td>\n",
       "      <td>0.621302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>b</td>\n",
       "      <td>1.440401</td>\n",
       "      <td>0.628700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  category      data   weights\n",
       "0        a  0.246188  0.001492\n",
       "1        a -1.459589  0.290795\n",
       "2        a -1.296136  0.885833\n",
       "3        a  0.069525  0.233582\n",
       "4        b  0.532954  0.593210\n",
       "5        b -1.948749  0.347098\n",
       "6        b  0.347697  0.621302\n",
       "7        b  1.440401  0.628700"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example three ：分组加权平均数和相关系数\n",
    "\n",
    "# 根据 groupby “ 拆分——应用——合并 ” 核心思想，\n",
    "# 可以进行 DataFrame 的列与列之间或者两个 Series 之间的运算（比如分组加权平均）\n",
    "\n",
    "# 以下数据集，含有分组键、值，以及一些权重值\n",
    "df = pd.DataFrame({'category': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'],\n",
    "                   'data': np.random.randn(8),\n",
    "                   'weights': np.random.rand(8)})\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category\n",
       "a   -1.102211\n",
       "b    0.347600\n",
       "dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 然后可以利用 category 计算分组加权平均数\n",
    "grouped = df.groupby('category')\n",
    "\n",
    "get_wavg = lambda g: np.average(g['data'], weights=g['weights'])\n",
    "\n",
    "grouped.apply(get_wavg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 2214 entries, 2003-01-02 to 2011-10-14\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   AAPL    2214 non-null   float64\n",
      " 1   MSFT    2214 non-null   float64\n",
      " 2   XOM     2214 non-null   float64\n",
      " 3   SPX     2214 non-null   float64\n",
      "dtypes: float64(4)\n",
      "memory usage: 86.5 KB\n"
     ]
    }
   ],
   "source": [
    "# 另一个例子， 考虑一个来自Yahoo!Finance的数据集\n",
    "#    其中含有几只股票和标准普尔500指数（符号SPX） 的收盘价\n",
    "\n",
    "dataset_path = './../dataset/'\n",
    "\n",
    "close_px = pd.read_csv(dataset_path + 'stock_px_2.csv', parse_dates=True, index_col=0)\n",
    "\n",
    "close_px.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AAPL</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>XOM</th>\n",
       "      <th>SPX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011-10-11</th>\n",
       "      <td>400.29</td>\n",
       "      <td>27.00</td>\n",
       "      <td>76.27</td>\n",
       "      <td>1195.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-12</th>\n",
       "      <td>402.19</td>\n",
       "      <td>26.96</td>\n",
       "      <td>77.16</td>\n",
       "      <td>1207.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-13</th>\n",
       "      <td>408.43</td>\n",
       "      <td>27.18</td>\n",
       "      <td>76.37</td>\n",
       "      <td>1203.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-14</th>\n",
       "      <td>422.00</td>\n",
       "      <td>27.27</td>\n",
       "      <td>78.11</td>\n",
       "      <td>1224.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              AAPL   MSFT    XOM      SPX\n",
       "2011-10-11  400.29  27.00  76.27  1195.54\n",
       "2011-10-12  402.19  26.96  77.16  1207.25\n",
       "2011-10-13  408.43  27.18  76.37  1203.66\n",
       "2011-10-14  422.00  27.27  78.11  1224.58"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "close_px[-4:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AAPL</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>XOM</th>\n",
       "      <th>SPX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>0.541124</td>\n",
       "      <td>0.745174</td>\n",
       "      <td>0.661265</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>0.374283</td>\n",
       "      <td>0.588531</td>\n",
       "      <td>0.557742</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>0.467540</td>\n",
       "      <td>0.562374</td>\n",
       "      <td>0.631010</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>0.428267</td>\n",
       "      <td>0.406126</td>\n",
       "      <td>0.518514</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>0.508118</td>\n",
       "      <td>0.658770</td>\n",
       "      <td>0.786264</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>0.681434</td>\n",
       "      <td>0.804626</td>\n",
       "      <td>0.828303</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>0.707103</td>\n",
       "      <td>0.654902</td>\n",
       "      <td>0.797921</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>0.710105</td>\n",
       "      <td>0.730118</td>\n",
       "      <td>0.839057</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>0.691931</td>\n",
       "      <td>0.800996</td>\n",
       "      <td>0.859975</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          AAPL      MSFT       XOM  SPX\n",
       "2003  0.541124  0.745174  0.661265  1.0\n",
       "2004  0.374283  0.588531  0.557742  1.0\n",
       "2005  0.467540  0.562374  0.631010  1.0\n",
       "2006  0.428267  0.406126  0.518514  1.0\n",
       "2007  0.508118  0.658770  0.786264  1.0\n",
       "2008  0.681434  0.804626  0.828303  1.0\n",
       "2009  0.707103  0.654902  0.797921  1.0\n",
       "2010  0.710105  0.730118  0.839057  1.0\n",
       "2011  0.691931  0.800996  0.859975  1.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 来做一个比较有趣的任务：\n",
    "#    计算一个由日收益率（通过百分数变化计算）与 SPX 之间的年度相关系数组成的DataFrame\n",
    "\n",
    "# 面是一个实现办法，先创建一个函数，用它计算 每列和 SPX列的成对相关系数：\n",
    "spx_corr = lambda x: x.corrwith(x['SPX'])\n",
    "\n",
    "# 接下来，使用 pct_change 计算 close_px 的百分比变化\n",
    "rets = close_px.pct_change().dropna()\n",
    "\n",
    "# 最后，用年对百分比变化进行分组，\n",
    "# 可以用一个一行的函数，从每行的标签返回每个 datetime 标签的 year 属性\n",
    "get_year = lambda x: x.year\n",
    "\n",
    "by_year = rets.groupby(get_year)\n",
    "\n",
    "by_year.apply(spx_corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2003    0.480868\n",
       "2004    0.259024\n",
       "2005    0.300093\n",
       "2006    0.161735\n",
       "2007    0.417738\n",
       "2008    0.611901\n",
       "2009    0.432738\n",
       "2010    0.571946\n",
       "2011    0.581987\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 当然，还可以计算列与列之间的相关系数\n",
    "# 这里，计算 Apple 和 Microsoft 的年相关系数\n",
    "by_year.apply(lambda g: g['AAPL'].corr(g['MSFT']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SPX</th>\n",
       "      <th>intercept</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>1.195406</td>\n",
       "      <td>0.000710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>1.363463</td>\n",
       "      <td>0.004201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>1.766415</td>\n",
       "      <td>0.003246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>1.645496</td>\n",
       "      <td>0.000080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>1.198761</td>\n",
       "      <td>0.003438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>0.968016</td>\n",
       "      <td>-0.001110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>0.879103</td>\n",
       "      <td>0.002954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>1.052608</td>\n",
       "      <td>0.001261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>0.806605</td>\n",
       "      <td>0.001514</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           SPX  intercept\n",
       "2003  1.195406   0.000710\n",
       "2004  1.363463   0.004201\n",
       "2005  1.766415   0.003246\n",
       "2006  1.645496   0.000080\n",
       "2007  1.198761   0.003438\n",
       "2008  0.968016  -0.001110\n",
       "2009  0.879103   0.002954\n",
       "2010  1.052608   0.001261\n",
       "2011  0.806605   0.001514"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example four ：组级别的线性回归\n",
    "\n",
    "# 顺着上一个例子继续， 可以用 groupby 执行更为复杂的分组统计分析， \n",
    "#    只要函数返回的是 pandas 对象或标量值即可。 \n",
    "\n",
    "# 例如， 可以定义下面这个 regress 函数（利用 statsmodels 计量经济学库）\n",
    "#       对各数据块执行普通最小二乘法（Ordinary Least Squares， OLS） 回归：\n",
    "\n",
    "# 约定写法\n",
    "import statsmodels.api as sm\n",
    "\n",
    "def regress(data, yvar, xvars):\n",
    "    Y = data[yvar]\n",
    "    X= data[xvars]\n",
    "    X['intercept'] = 1\n",
    "    result = sm.OLS(Y, X,).fit()\n",
    "    return result.params\n",
    "\n",
    "# 现在，为了按年计算 AAPL 对 SPX 收益率的线性回归\n",
    "by_year.apply(regress, 'AAPL', ['SPX'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 透视表 和 交叉表\n",
    "\n",
    "# 透视表（pivot table）是各种电子表格程序和其他数据分析软件中常见的数据汇总工具\n",
    "# 根据一个或者对个键对数据进行聚合，并根据行 和 列 的分组键将数据分配到各个矩形区域中\n",
    "\n",
    "# 在 Python 和 pandas 中，通过 groupby 功能以及（能够利用层次化索引）重塑运算 制作透视表\n",
    "# DataFrame 还有一个 pivot_table 方法\n",
    "# 还有一个顶级的 pandas.pivot_table 函数\n",
    "# 除了为 groupby 提供便利之外，pivot_table 还可以添加分项小计，也叫作 margins\n",
    "\n",
    "# 小费数据集，假设想要根据 day 和 smoker 计算分组平均数（pivot_table 默认聚合类型），并将 day 和 smoker 放到行上\n",
    "\n",
    "tips = pd.read_csv(dataset_path + 'tips.csv')\n",
    "\n",
    "tips.pivot_table(index=['day', 'smoker'])\n",
    "\n",
    "tips['tip_pct'] = tips['tip'] / tips['total_bill']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">size</th>\n",
       "      <th colspan=\"2\" halign=\"left\">tip_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>day</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Dinner</th>\n",
       "      <th>Fri</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.222222</td>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.165347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <td>2.555556</td>\n",
       "      <td>2.476190</td>\n",
       "      <td>0.158048</td>\n",
       "      <td>0.147906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <td>2.929825</td>\n",
       "      <td>2.578947</td>\n",
       "      <td>0.160113</td>\n",
       "      <td>0.187250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.159744</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>Fri</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.833333</td>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.188937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>2.500000</td>\n",
       "      <td>2.352941</td>\n",
       "      <td>0.160311</td>\n",
       "      <td>0.163863</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 size             tip_pct          \n",
       "smoker             No       Yes        No       Yes\n",
       "time   day                                         \n",
       "Dinner Fri   2.000000  2.222222  0.139622  0.165347\n",
       "       Sat   2.555556  2.476190  0.158048  0.147906\n",
       "       Sun   2.929825  2.578947  0.160113  0.187250\n",
       "       Thur  2.000000       NaN  0.159744       NaN\n",
       "Lunch  Fri   3.000000  1.833333  0.187735  0.188937\n",
       "       Thur  2.500000  2.352941  0.160311  0.163863"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以用 groupby 直接来做\n",
    "#    现在，假设只想聚合 tip_pct 和 size，而且想根据 time 进行分组\n",
    "# 将 smoker 放到列上，把 day 放到行上\n",
    "tips.pivot_table(['tip_pct', 'size'], \n",
    "                 index=['time', 'day'],\n",
    "                 columns='smoker')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">size</th>\n",
       "      <th colspan=\"3\" halign=\"left\">tip_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>All</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>day</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Dinner</th>\n",
       "      <th>Fri</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.222222</td>\n",
       "      <td>2.166667</td>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.165347</td>\n",
       "      <td>0.158916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <td>2.555556</td>\n",
       "      <td>2.476190</td>\n",
       "      <td>2.517241</td>\n",
       "      <td>0.158048</td>\n",
       "      <td>0.147906</td>\n",
       "      <td>0.153152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <td>2.929825</td>\n",
       "      <td>2.578947</td>\n",
       "      <td>2.842105</td>\n",
       "      <td>0.160113</td>\n",
       "      <td>0.187250</td>\n",
       "      <td>0.166897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.159744</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.159744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>Fri</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.833333</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.188937</td>\n",
       "      <td>0.188765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>2.500000</td>\n",
       "      <td>2.352941</td>\n",
       "      <td>2.459016</td>\n",
       "      <td>0.160311</td>\n",
       "      <td>0.163863</td>\n",
       "      <td>0.161301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>2.668874</td>\n",
       "      <td>2.408602</td>\n",
       "      <td>2.569672</td>\n",
       "      <td>0.159328</td>\n",
       "      <td>0.163196</td>\n",
       "      <td>0.160803</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 size                       tip_pct                    \n",
       "smoker             No       Yes       All        No       Yes       All\n",
       "time   day                                                             \n",
       "Dinner Fri   2.000000  2.222222  2.166667  0.139622  0.165347  0.158916\n",
       "       Sat   2.555556  2.476190  2.517241  0.158048  0.147906  0.153152\n",
       "       Sun   2.929825  2.578947  2.842105  0.160113  0.187250  0.166897\n",
       "       Thur  2.000000       NaN  2.000000  0.159744       NaN  0.159744\n",
       "Lunch  Fri   3.000000  1.833333  2.000000  0.187735  0.188937  0.188765\n",
       "       Thur  2.500000  2.352941  2.459016  0.160311  0.163863  0.161301\n",
       "All          2.668874  2.408602  2.569672  0.159328  0.163196  0.160803"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 还可以对这个表作进一步的处理， 传入 margins=True 添加分项小计。 \n",
    "# 这将会添加标签为 All的行和列， 其值对应于单个等级中所有数据的分组统计：\n",
    "tips.pivot_table(['tip_pct', 'size'], index=['time', 'day'], columns='smoker', margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th>Fri</th>\n",
       "      <th>Sat</th>\n",
       "      <th>Sun</th>\n",
       "      <th>Thur</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>smoker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Dinner</th>\n",
       "      <th>No</th>\n",
       "      <td>3.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>9.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>No</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>19.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>244.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day             Fri   Sat   Sun  Thur    All\n",
       "time   smoker                               \n",
       "Dinner No       3.0  45.0  57.0   1.0  106.0\n",
       "       Yes      9.0  42.0  19.0   NaN   70.0\n",
       "Lunch  No       1.0   NaN   NaN  44.0   45.0\n",
       "       Yes      6.0   NaN   NaN  17.0   23.0\n",
       "All            19.0  87.0  76.0  62.0  244.0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 这里， All值为平均数： 不单独考虑烟民与非烟民（All列） ，不单独考虑行分组两个级别中的任何单项（All行） 。\n",
    "\n",
    "\n",
    "# 要使用其他的聚合函数， 将其传给 aggfunc 即可\n",
    "#  例如， 使用 count 或 len 可以得到有关分组大小的交叉表（计数或频率）\n",
    "tips.pivot_table('tip_pct', index=['time', 'smoker'], columns='day', aggfunc=len, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th>Fri</th>\n",
       "      <th>Sat</th>\n",
       "      <th>Sun</th>\n",
       "      <th>Thur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>size</th>\n",
       "      <th>smoker</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">Dinner</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.325733</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>No</th>\n",
       "      <td>0.139622</td>\n",
       "      <td>0.162705</td>\n",
       "      <td>0.168859</td>\n",
       "      <td>0.159744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.171297</td>\n",
       "      <td>0.148668</td>\n",
       "      <td>0.207893</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.154661</td>\n",
       "      <td>0.152663</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.144995</td>\n",
       "      <td>0.152660</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.150096</td>\n",
       "      <td>0.148143</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.117750</td>\n",
       "      <td>0.124515</td>\n",
       "      <td>0.193370</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">5</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.206928</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.106572</td>\n",
       "      <td>0.065660</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.103799</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">Lunch</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.181728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.223776</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.181969</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.158843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>No</th>\n",
       "      <td>0.187735</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.084246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.204952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.138919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yes</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.155410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.121389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>No</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.173706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "day                      Fri       Sat       Sun      Thur\n",
       "time   size smoker                                        \n",
       "Dinner 1    No      0.000000  0.137931  0.000000  0.000000\n",
       "            Yes     0.000000  0.325733  0.000000  0.000000\n",
       "       2    No      0.139622  0.162705  0.168859  0.159744\n",
       "            Yes     0.171297  0.148668  0.207893  0.000000\n",
       "       3    No      0.000000  0.154661  0.152663  0.000000\n",
       "            Yes     0.000000  0.144995  0.152660  0.000000\n",
       "       4    No      0.000000  0.150096  0.148143  0.000000\n",
       "            Yes     0.117750  0.124515  0.193370  0.000000\n",
       "       5    No      0.000000  0.000000  0.206928  0.000000\n",
       "            Yes     0.000000  0.106572  0.065660  0.000000\n",
       "       6    No      0.000000  0.000000  0.103799  0.000000\n",
       "Lunch  1    No      0.000000  0.000000  0.000000  0.181728\n",
       "            Yes     0.223776  0.000000  0.000000  0.000000\n",
       "       2    No      0.000000  0.000000  0.000000  0.166005\n",
       "            Yes     0.181969  0.000000  0.000000  0.158843\n",
       "       3    No      0.187735  0.000000  0.000000  0.084246\n",
       "            Yes     0.000000  0.000000  0.000000  0.204952\n",
       "       4    No      0.000000  0.000000  0.000000  0.138919\n",
       "            Yes     0.000000  0.000000  0.000000  0.155410\n",
       "       5    No      0.000000  0.000000  0.000000  0.121389\n",
       "       6    No      0.000000  0.000000  0.000000  0.173706"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 如果存在空的组合（也就是NA） ， 你可能会希望设置一个 fill_value：\n",
    "tips.pivot_table('tip_pct', index=['time', 'size', 'smoker'], \n",
    "                 columns='day', aggfunc='mean', fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pivot_table 的参数说明\n",
    "\n",
    "# 参数名        说明\n",
    "# values       待聚合的列的名称。默认聚合所有数值列\n",
    "# index        用于分组的列名或其他分组键，出现在结果透视表的行\n",
    "# columns      用于分组的列名或其他分组键，出现在结果透视表的列\n",
    "# aggfunc      聚合函数或函数列表，默认为mean。可以是任何对groupby有效的函数\n",
    "# fill _value  用于替换结果表中的缺失值\n",
    "# dropna       如果为True，不添加条目都为NA的列\n",
    "# margins      添加行/列小计和总计，默认为False\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sample</th>\n",
       "      <th>Nationality</th>\n",
       "      <th>Handedness</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>USA</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Left-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>USA</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Left-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>USA</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>USA</td>\n",
       "      <td>Left-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>USA</td>\n",
       "      <td>Right-handed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Sample Nationality    Handedness\n",
       "0       1         USA  Right-handed\n",
       "1       2       Japan   Left-handed\n",
       "2       3         USA  Right-handed\n",
       "3       4       Japan  Right-handed\n",
       "4       5       Japan   Left-handed\n",
       "5       6       Japan  Right-handed\n",
       "6       7         USA  Right-handed\n",
       "7       8         USA   Left-handed\n",
       "8       9       Japan  Right-handed\n",
       "9      10         USA  Right-handed"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 交叉表 crosstab\n",
    "\n",
    "# 交叉表（ cross-tabulation ，简称 crosstab ）是一种用于计算分组频率的特殊透视表\n",
    "\n",
    "from io import StringIO\n",
    "data = \"\"\"\\\n",
    "Sample  Nationality  Handedness\n",
    "1   USA  Right-handed\n",
    "2   Japan    Left-handed\n",
    "3   USA  Right-handed\n",
    "4   Japan    Right-handed\n",
    "5   Japan    Left-handed\n",
    "6   Japan    Right-handed\n",
    "7   USA  Right-handed\n",
    "8   USA  Left-handed\n",
    "9   Japan    Right-handed\n",
    "10  USA  Right-handed\"\"\"\n",
    "data = pd.read_table(StringIO(data), sep='\\s+')\n",
    "\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Handedness</th>\n",
       "      <th>Left-handed</th>\n",
       "      <th>Right-handed</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nationality</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Handedness   Left-handed  Right-handed  All\n",
       "Nationality                                \n",
       "Japan                  2             3    5\n",
       "USA                    1             4    5\n",
       "All                    3             7   10"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 作为调查分析的一部分， 可能想要根据国籍和用手习惯对这段数据进行统计汇总。 \n",
    "# 虽然可以用 pivot_table 实现该功能， 但是 pandas.crosstab 函数会更方便：\n",
    "pd.crosstab(data.Nationality, data.Handedness, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>smoker</th>\n",
       "      <th>No</th>\n",
       "      <th>Yes</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>day</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Dinner</th>\n",
       "      <th>Fri</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sat</th>\n",
       "      <td>45</td>\n",
       "      <td>42</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sun</th>\n",
       "      <td>57</td>\n",
       "      <td>19</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Lunch</th>\n",
       "      <th>Fri</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thur</th>\n",
       "      <td>44</td>\n",
       "      <td>17</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>151</td>\n",
       "      <td>93</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "smoker        No  Yes  All\n",
       "time   day                \n",
       "Dinner Fri     3    9   12\n",
       "       Sat    45   42   87\n",
       "       Sun    57   19   76\n",
       "       Thur    1    0    1\n",
       "Lunch  Fri     1    6    7\n",
       "       Thur   44   17   61\n",
       "All          151   93  244"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# crosstab 的前两个参数可以是 数组 或 Series， 或是数组列表\n",
    "#   就像小费数据：\n",
    "pd.crosstab([tips.time, tips.day], tips.smoker, margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 掌握 pandas 数据分组工具 既有利于数据清洗，也有利于建模或者统计分析工作"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
